package com.css.fxfzypg.modules.baseData.repository.impl;

import com.alibaba.fastjson.JSONObject;
import com.css.fxfzypg.constants.FxfzConstants;
import com.css.fxfzypg.constants.YNEnum;
import com.css.fxfzypg.modules.baseData.entity.VillageTownCondition;
import com.css.fxfzypg.modules.baseData.queryParams.VillageTownConditionQueryParams;
import com.css.fxfzypg.modules.baseData.repository.VillageTownConditionRepository;
import com.css.fxfzypg.util.CreateGson;
import com.css.fxfzypg.util.PlatformDateUtils;
import com.css.fxfzypg.util.PlatformObjectUtils;
import com.css.fxfzypg.util.PlatformSessionUtils;
import com.css.fxfzypg.web.PlatformSessionContext;
import com.css.fxfzypg.zcpt.sys.entity.SUser;
import com.css.fxfzypg.zcpt.sys.service.SUserService;
import com.css.fxfzypg.zcpt.sys.service.SysRoleService;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.sql.Timestamp;
import java.util.List;
import java.util.Map;

/**
 * @author Dbj
 * @date 2022年11月14日  10:53
 */
@Repository
public class VillageTownConditionRepositoryImpl implements VillageTownConditionRepository {
    @Resource
    JdbcTemplate jdbcTemplate;
    @Resource
    NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    @Resource
    SysRoleService sysRoleService;

    @Resource
    SUserService sUserService;

    @Override
    public JSONObject getAllVillageTownCondition(VillageTownConditionQueryParams queryParams, int curPage, int pageSize) {
        StringBuilder str=new StringBuilder("");
        StringBuilder sql = new StringBuilder(" select  c.* from sys_village_town_condition c ");
        StringBuilder whereSql = new StringBuilder(" where c.is_delete = '"+ YNEnum.N.toString()+"'");
        if (!PlatformObjectUtils.isEmpty(queryParams.getProvinceId())) {
            whereSql.append(" and c.province_id ='" + queryParams.getProvinceId() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getCityId())) {
            whereSql.append(" and c.city_id  ='" + queryParams.getCityId() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getPrefectureId())) {
            whereSql.append(" and c.prefecture_id ='" + queryParams.getPrefectureId() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getVillageTown())) {
            whereSql.append(" and c.village_town like '%"+ queryParams.getVillageTown() +"%'");
        }
        String userId = PlatformSessionContext.getUserID();
        String user = sUserService.getSUser(userId);
        SUser sUser = CreateGson.createGson().fromJson(user, SUser.class);
        String role = sysRoleService.getRoleByUserId(userId);
        List<Map> roleMaps = JSONObject.parseArray(role, Map.class);
        for(Map roleMap:roleMaps){
            str.append(roleMap.get("roleCode")+",");
        }
        String roles = str.toString();
        if(roles.contains(FxfzConstants.YPG_COUN_GL_ROLE) || roles.contains(FxfzConstants.YPG_COUN_YW_ROLE)){
            //国家级管理员和国家级业务人员可以全看全部
        }else if(roles.contains(FxfzConstants.YPG_PRI_GL_ROLE) || roles.contains(FxfzConstants.YPG_PRI_YW_ROLE)){
            //省级管理员和省级业务人员可以全看自己
            whereSql.append(" and c.province_id = '"+sUser.getProvinceId()+"'");
        }
        StringBuilder orderSql = new StringBuilder();
        if ("0".equals(queryParams.getOption())){
            orderSql.append(" order by c.province_code,c.city_code,c.prefecture_code");
        }
        if ("1".equals(queryParams.getOption())){
            orderSql.append(" order by COALESCE (c.update_time,c.create_time) desc");
        }
        sql.append(whereSql).append(orderSql).toString();
        int firstIndex = (curPage - 1) * pageSize;
        int lastIndex =  pageSize;
        StringBuilder limitSql = new StringBuilder(" limit " + lastIndex + " offset " + firstIndex);
        Integer total = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(VillageTownCondition.class)).size();
        List<VillageTownCondition> entities = jdbcTemplate.query(sql.append(limitSql).toString(), new BeanPropertyRowMapper<>(VillageTownCondition.class));
        JSONObject jsonObject = new JSONObject();
        jsonObject.put("rows", entities);
        jsonObject.put("total", total);
        return jsonObject;
    }

    @Override
    public int getVillageTownConditionBydivision(String provinceId, String cityId, String prefectureId, String villageTown) {
        String sql ="select count(*) from sys_village_town_condition where province_id ='" + provinceId + "' and city_id ='" + cityId + "'" +
                " and prefecture_id ='" + prefectureId + "' and village_town ='"+villageTown+"' and is_delete='" + YNEnum.N.toString() + "'";
        return jdbcTemplate.queryForObject(sql, Integer.class);
    }

    @Override
    public int saveVillageTownCondition(VillageTownCondition villageTownCondition) {
        String sql = "insert into sys_village_town_condition(cc_id,province,city,prefecture,village_town,cc_landform," +
                "cc_seismotectonics,cc_climate,cc_nation,cc_economy,cc_traffic,cc_disaster,cc_facility," +
                "cc_trait,cc_school,cc_hospital,cc_hazard,cc_reservoir,cc_message,cc_landform_attachment," +
                "cc_seismotectonics_attachment,cc_climate_attachment,cc_nation_attachment,cc_economy_attachment," +
                "cc_traffic_attachment,cc_disaster_attachment,cc_facility_attachment," +
                "cc_trait_attachment,cc_school_attachment,cc_hospital_attachment,cc_hazard_attachment," +
                "cc_reservoir_attachment,cc_message_attachment,is_delete,create_user,create_time,province_id,city_id," +
                "prefecture_id,province_code,city_code,prefecture_code) " +
                "values(:ccId,:province,:city,:prefecture,:villageTown,:ccLandform,:ccSeismotectonics,:ccClimate,:ccNation," +
                ":ccEconomy,:ccTraffic,:ccDisaster,:ccFacility,:ccTrait,:ccSchool,:ccHospital,:ccHazard," +
                ":ccReservoir,:ccMessage,:ccLandformAttachment,:ccSeismotectonicsAttachment,:ccClimateAttachment," +
                ":ccNationAttachment,:ccEconomyAttachment,:ccTrafficAttachment,:ccDisasterAttachment," +
                ":ccFacilityAttachment,:ccTraitAttachment,:ccSchoolAttachment," +
                ":ccHospitalAttachment,:ccHazardAttachment,:ccReservoirAttachment,:ccMessageAttachment," +
                ":isDelete,:createUser,:createTime,:provinceId,:cityId,:prefectureId,:provinceCode,:cityCode,:prefectureCode)";
        SqlParameterSource source = new BeanPropertySqlParameterSource(villageTownCondition);
        //拿到新插入的主键值（i）
        KeyHolder keyHolder = new GeneratedKeyHolder();
        return namedParameterJdbcTemplate.update(sql, source, keyHolder);
    }

    @Override
    public VillageTownCondition getVillageTownConditionById(String id) {
        String sql = "select * from sys_village_town_condition where cc_id = '" + id + "' and is_delete = '"+YNEnum.N.toString()+"'";
        List<VillageTownCondition> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(VillageTownCondition.class));
        return list.size()>0?list.get(0):new VillageTownCondition();
    }

    @Override
    public void delete(String id, Timestamp timestamp, String userId) {
        String sql ="update sys_village_town_condition set is_delete = '"+YNEnum.Y.toString()+
                "', update_time = '" + timestamp + "', update_user = '" + userId + "' where cc_id ='"+id+"' and is_delete = '"+YNEnum.N.toString()+"'";
        jdbcTemplate.update(sql);
    }

    @Override
    public void update(VillageTownCondition villageTownCondition) {
        String sql = "update sys_village_town_condition set cc_landform = :ccLandform, " +
                "cc_seismotectonics = :ccSeismotectonics, cc_climate = :ccClimate, cc_nation = :ccNation, " +
                "cc_economy = :ccEconomy, cc_traffic = :ccTraffic, cc_disaster = :ccDisaster, " +
                "cc_facility = :ccFacility, cc_trait = :ccTrait, cc_school = :ccSchool, " +
                "cc_hospital = :ccHospital, cc_hazard = :ccHazard, cc_reservoir = :ccReservoir, cc_message = " +
                ":ccMessage,cc_landform_attachment = :ccLandformAttachment, " +
                "cc_seismotectonics_attachment = :ccSeismotectonicsAttachment, " +
                "cc_climate_attachment = :ccClimateAttachment, cc_nation_attachment = :ccNationAttachment, "+
                "cc_economy_attachment = :ccEconomyAttachment, cc_traffic_attachment = :ccTrafficAttachment, " +
                "cc_disaster_attachment = :ccDisasterAttachment, cc_facility_attachment = :ccFacilityAttachment, " +
                "cc_trait_attachment = :ccTraitAttachment, " +
                "cc_school_attachment = :ccSchoolAttachment, cc_hospital_attachment = :ccHospitalAttachment, " +
                "cc_hazard_attachment = :ccHazardAttachment, cc_reservoir_attachment = :ccReservoirAttachment, " +
                "cc_message_attachment = :ccMessageAttachment, update_user = :updateUser, update_time = :updateTime " +
                "where cc_id = :ccId and is_delete = '"+YNEnum.N.toString()+"'";
        SqlParameterSource source = new BeanPropertySqlParameterSource(villageTownCondition);
        //拿到新插入的主键值（i）
        KeyHolder keyHolder = new GeneratedKeyHolder();
        namedParameterJdbcTemplate.update(sql, source, keyHolder);
    }

    @Override
    public VillageTownCondition getVillageCountiesCodition(String province, String city, String area, String township) {
        String sql ="select * from sys_village_town_condition where province ='" + province + "' and city ='" + city + "'" +
                " and prefecture ='" + area + "' and village_town ='"+township+"' and is_delete='" + YNEnum.N.toString() + "'";
        List<VillageTownCondition> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(VillageTownCondition.class));
        return list.size()>0?list.get(0):null;
    }

    @Override
    public void deleteByVillageTown(VillageTownCondition condition) {
        String userId = PlatformSessionUtils.getUserId();
        Timestamp currentTimestamp = PlatformDateUtils.getCurrentTimestamp();
        String sql = "update sys_village_town_condition set is_delete = '" + YNEnum.Y.toString() +
                "' ,update_time ='" + currentTimestamp + "', update_user='" + userId + "'" +
                " where province = '" + condition.getProvince() + "' and city = '" + condition.getCity()+
                "' and prefecture ='"+condition.getPrefecture()+"' and village_town='"+condition.getVillageTown()+"'";
        jdbcTemplate.update(sql);
    }

    @Override
    public Integer countByProvinceAndYear(String province, String taskYear) {
        return jdbcTemplate.queryForObject("SELECT count(*) from sys_village_town_condition where province = ? and (TO_CHAR(update_time, 'yyyy-MM-dd hh24:mi:ss') LIKE ? or TO_CHAR(create_time, 'yyyy-MM-dd hh24:mi:ss') LIKE ?)  and is_delete ='0'", Integer.class, province, taskYear + "%", taskYear + "%");
    }
}
